<?php

namespace App\Services;

use App\AwardAndExpertGroupRelations;
use App\AwardApplicationFormData;
use App\AwardGroupAndExpertGroupRelations;
use App\Consts;
use App\ConvertTask;
use App\ExpertAndExpertGroupRelations;
use App\ExpertGroup;
use App\Jobs\ConvertAwardFormDataToPdfJob;
use App\MeetingReviewProject;
use App\Project;
use App\ReviewerGroupProject;
use App\User;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use stdClass;
use App\AwardGroup;
use App\AwardAndAwardGroupRelations;

class ProjectService
{
    private $workflowSvc;

    public function __construct()
    {
        $this->awardsConfig = config('vars.awards');
        $this->logSvc = new LogService;
        $this->workflowSvc = new WorkflowService();
    }

    public function getProjectFormData($projectId)
    {
        $record = AwardApplicationFormData::find($projectId);
        $formData = json_decode($record->form_data);
        if (empty($formData)) {
            $formData = (object)[];
        }
        return $formData;
    }

    public function projectFormalReviewPassed($projectId)
    {
        try {
            $workflowSvc = new WorkflowService();
        } catch (\Exception $e) {
            return [
                'ok' => false,
                'code' => 3,
                'msg' => '构造 WorkflowService 实例失败',
            ];
        }
        $project = Project::where('id', $projectId)->select('id', 'award_id', 'status_id', 'prof_category')->first();
        if ($project->award_id === 1 && $workflowSvc->notAtPhase(WorkflowService::PHASE_FORMAL_REVIEW)) {
            return [
                'ok' => false,
                'code' => 4,
                'msg' => '现在不是形式审查阶段，不能执行该操作',
            ];
        }
        try {
            $largeGroups = DB::table('large_groups')->get();
            $awardId = $project->award_id;
            $largeGroupId = null;
            foreach ($largeGroups as $largeGroup) {
                if ($awardId != $largeGroup->award_id) {
                    continue;
                }
                if ($awardId == Consts::AWARD_TYPE_TALENT) {
                    $largeGroupId = $largeGroup->id;
                    break;
                }
                $largeGroupAttrs = json_decode($largeGroup->attrs);
                if (in_array($project->prof_category, $largeGroupAttrs->project->prof_fields)) {
                    $largeGroupId = $largeGroup->id;
                    break;
                }
            }

            /**
             * 20200602@4
             * @修改
             * @需求：科技人物奖，通过形式审查后，直接进入会评
             * @time 2020-06-02
             * @start
             */

            if ($awardId == Consts::AWARD_TYPE_TALENT) {
                $meetingRanking = 1 + Project::where('large_group_id', $largeGroupId)
                    ->where('status_id', Project::STATUS_MEETING_REVIEW_TO_REVIEW)
                    ->max('meeting_ranking');
                Project::where('id', $projectId)->update([
                    'status_id' => Project::STATUS_MEETING_REVIEW_TO_REVIEW,
                    'large_group_id' => $largeGroupId,
                    'meeting_ranking' => $meetingRanking,
                ]);
                $meeting = new MeetingReviewProject();
                $meeting->project_id = $projectId;
                $meeting->large_group_id = $largeGroupId;
                $meeting->review_status = '待评审';
                $meeting->save();

            } else {
                Project::where('id', $projectId)->update([
                    'status_id' => Project::STATUS_ASSIGN_REVIEW_TO_ASSIGN,
                    'large_group_id' => $largeGroupId,
                ]);
            }
            /**
             * 20200602@4
             * @end
             */

            return [
                'ok' => true,
                'code' => 0,
                'msg' => 'ok',
            ];
        } catch (\Exception $e) {
            Log::error('', ['exception' => $e]);
        }
    }

    private function getExpertReviewProjects($largeGroupId)
    {
        $reviewerGroupProjects = DB::table('reviewer_group_projects')
            ->where('large_group_id', $largeGroupId)
            ->select('project_id', 'reviewer_group_id', 'avg_score', 'score_status',
                'large_group_leader_review_result', 'large_group_leader_review_remark')
            ->orderBy('avg_score', 'desc')
            ->get()
            ->toArray();
        foreach ($reviewerGroupProjects as $reviewerGroupProject) {
            $avg_score = $reviewerGroupProject->avg_score;
            if ($avg_score !== null) {
                $reviewerGroupProject->avg_score = (float)$avg_score;
            }
        }
        $projectId2Project = [];
        foreach ($reviewerGroupProjects as $reviewerGroupProject) {
            $reviewerGroupProject->score_status_phrase = ReviewerGroupProject::getScoreStatusPhraseById($reviewerGroupProject->score_status);
            $projectId2Project[$reviewerGroupProject->project_id] = $reviewerGroupProject;
        }

        $rows = DB::table('reviewer_score_projects as a')
            ->where('a.large_group_id', $largeGroupId)
            ->leftJoin('applications as b', 'a.project_id', '=', 'b.id')
            ->leftJoin('users as c', 'b.user_id', '=', 'c.id')
            ->select('a.project_id', 'a.reviewer_id', 'a.reviewer_group_id', 'a.reviewer_group_review_order',
                'a.score_time_cost', 'a.score', 'a.is_submitted', 'a.is_evidence_material_valid', 'a.remark',
                'b.project_name', 'b.status_id', 'b.final_award_level',
                'c.name as applicant', 'c.name as applicant_name', 'c.tel as applicant_tel', 'c.email as applicant_email')
            ->get()->toArray();
        foreach ($rows as $row) {
            $projectId = $row->project_id;
            $project = $projectId2Project[$projectId];
            if (!isset($project->project_name)) {
                $project->project_name = $row->project_name;
                $project->applicant = $row->applicant;
                $project->applicant_name = $row->applicant_name;
                $project->applicant_tel = $row->applicant_tel;
                $project->applicant_email = $row->applicant_email;
                $project->status_id = $row->status_id;
                $project->scores_normal = null;
                $project->scores_desc = null;
                $project->score_per_reviewer = [];
                $project->final_award_level = $row->final_award_level;
            }
            $project->score_per_reviewer[] = $row->is_submitted ? (object)[
                'score_time_cost' => $row->score_time_cost,
                'score' => $row->score,
                'is_evidence_material_valid' => $row->is_evidence_material_valid,
                'remark' => $row->remark,
            ] : (object)[
                'score_time_cost' => null,
                'score' => null,
                'is_evidence_material_valid' => null,
                'remark' => null,
            ];
        }

        // 计算专家组各专家评分的分值状态
        foreach ($projectId2Project as $project) {
            $reviewerScores = array_map(function ($reviewerScore) {
                return $reviewerScore->score;
            }, $project->score_per_reviewer);
            $scoresState = ReviewerGroupProject::calcScoresState($reviewerScores);
            $project->scores_normal = $scoresState['ok'];
            $project->scores_desc = $scoresState['desc'];
        }

        $projects = array_values($projectId2Project);
//        usort($projects, function ($a, $b) {
//            return $a->project_id - $b->project_id;
//        });

        return [
            'ok' => true,
            'code' => 0,
            'msg' => 'ok',
            'large_group_id' => $largeGroupId,
            'projects' => $projects,
        ];
    }

    /**
     * 大组长获取专家评审阶段的项目
     * @param $largeGroupId
     * @return array 模式:
     * - ok: bool
     * - code: int
     * - msg: string
     * - large_group_id: int
     * - projects: array[object]，按项目id升序排序
     *   - project_id: int
     *   - reviewer_group_id: int
     *   - project_name
     *   - applicant: string, 申报者名称
     *   - applicant_name : string, 申报者名称
     *   - applicant_tel : string, 申报者电话
     *   - applicant_email : string, 申报者邮箱
     *   - status_id: string, 项目状态id
     *   - score_status: int, 专家组评分状态id，ReviewerGroupProject::SCORE_STATUS_ID_XXX 常量
     *   - score_status_phrase: string, 专家组评分状态短语
     *   - avg_score: float
     *   - scores_normal: bool, 专家组各专家的打分分值是否正常
     *   - scores_desc: string, 专家组各专家的打分分值情况的描述
     *   - score_per_reviewer: array[object], 每个专家的打分数据
     *     - score_time_cost: int, nullable。评分时长（秒）
     *     - score: int, nullable
     *     - is_evidence_material_valid: int(1|0)
     *     - remark: string, nullable. 专家意见
     *   - large_group_leader_review_result
     *   - large_group_leader_review_remark
     */
    public function leaderGetExpertReviewProjects($largeGroupId)
    {
        $largeGroupId = (int)$largeGroupId;
        if (!$this->workflowSvc->hasPhaseBegun(WorkflowService::PHASE_EXPERT_REVIEW, $largeGroupId)) {
            return [
                'ok' => true,
                'code' => 0,
                'msg' => 'ok',
                'large_group_id' => $largeGroupId,
                'projects' => [],
            ];
        }

        return $this->getExpertReviewProjects($largeGroupId);
    }

    public function adminGetExpertReviewProjects($largeGroupId)
    {
        return $this->getExpertReviewProjects($largeGroupId);
    }

    /**
     * 大组长获取会评阶段的项目
     * @param $largeGroupId
     * @return array 模式:
     * - ok: bool
     * - code: int
     * - msg: string
     * - large_group_id: int
     * - projects: array[object]
     *   - project_id: int
     *   - reviewer_group_id: int
     *   - project_name
     *   - applicant: string, 申报者
     *   - status_id: string, 项目状态id
     *   - score_status: int, 专家组评分状态id，ReviewerGroupProject::SCORE_STATUS_ID_XXX 常量
     *   - score_status_phrase: string, 专家组评分状态短语
     *   - avg_score: float
     *   - scores_normal: bool, 专家组各专家的打分分值是否正常
     *   - scores_desc: string, 专家组各专家的打分分值情况的描述
     *   - score_per_reviewer: array[object], 每个专家的打分数据
     *     - score_time_cost: int, nullable。评分时长（秒）
     *     - score: int, nullable
     *     - is_evidence_material_valid: int(1|0)
     *     - remark: string, nullable. 专家意见
     *   - large_group_leader_review_result
     *   - large_group_leader_review_remark
     *   - meeting_review_status: string, 会评结果，MeetingReviewProject::REVIEW_STATUS_XXX 常量
     *   - meeting_review_remark: string, 会评备注
     */
    public function leaderGetMeetingReviewProjects($largeGroupId)
    {
        $largeGroupId = (int)$largeGroupId;
        if ($largeGroupId == 5) {
            //科技人物奖
            return $this->talentAwardNamelyLargeGroupId5();
        }
        $leaderGetExpertReviewProjectsResult = $this->leaderGetExpertReviewProjects($largeGroupId);
        if (!$leaderGetExpertReviewProjectsResult['ok']) {
            return [
                'ok' => false,
                'code' => 1,
                'msg' => $leaderGetExpertReviewProjectsResult['msg'],
            ];
        }

        if (empty($leaderGetExpertReviewProjectsResult['projects'])) {
            return [
                'ok' => true,
                'code' => 0,
                'msg' => 'ok',
                'large_group_id' => $largeGroupId,
                'awards_configure' => [],
                'projects' => [],
            ];
        }

        $expertReviewProjects = $leaderGetExpertReviewProjectsResult['projects'];
        $projectId2ExpertReviewProject = [];
        foreach ($expertReviewProjects as $expertReviewProject) {
            $projectId2ExpertReviewProject[$expertReviewProject->project_id] = $expertReviewProject;
        }

        $projects = [];
        $meetingReviewProjects = DB::table('meeting_review_projects as m')
            ->leftJoin('applications as a', 'm.project_id', '=', 'a.id')
            ->where('m.large_group_id', $largeGroupId)
            ->select('m.*', 'a.meeting_ranking', 'a.review_ranking', 'a.meeting_initial_ranking', 'a.award')
            ->orderBy('a.meeting_ranking')
            ->get()
            ->toArray();

        foreach ($meetingReviewProjects as $i => $meetingReviewProject) {
            $projectId = $meetingReviewProject->project_id;
            $project = $projectId2ExpertReviewProject[$projectId];
            $project->meeting_review_status = $meetingReviewProject->review_status;
            $project->meeting_review_remark = $meetingReviewProject->review_remark;
            $project->meeting_ranking = $meetingReviewProject->meeting_ranking;
            $project->review_ranking = $meetingReviewProject->review_ranking;
            $project->award = $meetingReviewProject->award;
            $project->meeting_initial_ranking = $meetingReviewProject->meeting_initial_ranking;
            $projects[] = $project;
        }

        //加入大组信息
        $largeGroupInfo = DB::table('large_groups')->where('id', $largeGroupId)->first();
        $attrs = json_decode($largeGroupInfo->attrs);
        if (!isset($attrs->awards_configure)) {
            $attrs->awards_configure = [
                (object)[
                    'key' => 1,
                    'title' => '一等奖',
                    'number' => 0,
                ],
                (object)[
                    'key' => 2,
                    'title' => '二等奖',
                    'number' => 0,
                ],
                (object)[
                    'key' => 3,
                    'title' => '三等奖',
                    'number' => 0,
                ]
            ];
        }

        return [
            'ok' => true,
            'code' => 0,
            'msg' => 'ok',
            'large_group_id' => $largeGroupId,
            'awards_configure' => $attrs->awards_configure,
            'projects' => $projects,
        ];
    }

    private function talentAwardNamelyLargeGroupId5()
    {
        $largeGroupId = 5;
        $projects = DB::table('meeting_review_projects as m')
            ->leftJoin('applications as a', 'm.project_id', '=', 'a.id')
            ->leftJoin('users as u', 'u.id', '=', 'a.user_id')
            ->where('m.large_group_id', $largeGroupId)
            ->select('m.*', 'm.review_status as meeting_review_status', 'm.review_status as meeting_review_remark',
                'a.avg_score', 'a.meeting_ranking', 'a.review_ranking', 'a.meeting_initial_ranking', 'a.award',
                'a.project_name', 'u.name as applicant_name', 'u.tel as applicant_tel', 'u.email as applicant_email')
            ->orderBy('a.meeting_ranking')
            ->get()
            ->toArray();

        //加入大组信息
        $largeGroupInfo = DB::table('large_groups')->where('id', $largeGroupId)->first();
        $attrs = json_decode($largeGroupInfo->attrs);
        if (!isset($attrs->awards_configure)) {
            $attrs->awards_configure = [
                (object)[
                    'key' => 1,
                    'title' => '科技人物奖',
                    'number' => 0,
                ],
            ];
        }

        return [
            'ok' => true,
            'code' => 0,
            'msg' => 'ok',
            'large_group_id' => $largeGroupId,
            'awards_configure' => $attrs->awards_configure,
            'projects' => $projects,
        ];
    }

    public function leaderGetDefenseProjects($largeGroupId)
    {
        $leaderGetExpertReviewProjectsResult = $this->leaderGetExpertReviewProjects($largeGroupId);
        if (!$leaderGetExpertReviewProjectsResult['ok']) {
            return [
                'ok' => false,
                'code' => 1,
                'msg' => $leaderGetExpertReviewProjectsResult['msg'],
            ];
        }
        $expertReviewProjects = $leaderGetExpertReviewProjectsResult['projects'];
        $defenseProjectIds = DB::table('defense_projects')->where('large_group_id', $largeGroupId)->pluck('project_id')->toArray();
        $defenseProjects = [];
        foreach ($expertReviewProjects as $expertReviewProject) {
            if (in_array($expertReviewProject->project_id, $defenseProjectIds)) {
                $defenseProjects[] = $expertReviewProject;
            }
        }
        usort($defenseProjects, function ($a, $b) {
            $a = $a->avg_score;
            $b = $b->avg_score;
            return $a < $b ? 1 : ($a == $b ? 0 : -1);
        });
        return [
            'ok' => true,
            'code' => 0,
            'msg' => 'ok',
            'projects' => $defenseProjects,
        ];
    }

    /**
     * 委员获取投票阶段的项目
     * @param $commissionerId
     * @param $largeGroupId
     * @return array 模式:
     * - ok: bool
     * - code: int
     * - msg: string
     * - large_group_id: int
     * - projects: array[object]，元素按 avg_score 降序排序
     *   - project_id: int
     *   - reviewer_group_id: int
     *   - project_name
     *   - applicant: string, 申报者
     *   - status_id: string, 项目状态id
     *   - score_status: int, 专家组评分状态id，ReviewerGroupProject::SCORE_STATUS_ID_XXX 常量
     *   - score_status_phrase: string, 专家组评分状态短语
     *   - avg_score: float
     *   - scores_normal: bool, 专家组各专家的打分分值是否正常
     *   - scores_desc: string, 专家组各专家的打分分值情况的描述
     *   - score_per_reviewer: array[object], 每个专家的打分数据
     *     - score_time_cost: int, nullable。评分时长（秒）
     *     - score: int, nullable
     *     - is_evidence_material_valid: int(1|0)
     *     - remark: string, nullable. 专家意见
     *   - vote_result: string, 投票结果，Consts::VOTE_RESULT_XXX 常量
     *   - vote_remark: string, 投票备注
     */
    public function committeeGetVoteProjects($commissionerId, $largeGroupId)
    {
        $largeGroupId = (int)$largeGroupId;
        $leaderGetExpertReviewProjectsResult = $this->leaderGetExpertReviewProjects($largeGroupId);
        if (!$leaderGetExpertReviewProjectsResult['ok']) {
            return [
                'ok' => false,
                'code' => 1,
                'msg' => $leaderGetExpertReviewProjectsResult['msg'],
            ];
        }
        $expertReviewProjects = $leaderGetExpertReviewProjectsResult['projects'];
        $projectId2ExpertReviewProject = [];
        foreach ($expertReviewProjects as $expertReviewProject) {
            $projectId2ExpertReviewProject[$expertReviewProject->project_id] = $expertReviewProject;
        }
        $commissionerProjectVotes = DB::table('commissioner_project_votes')
            ->where('commissioner_id', $commissionerId)
            ->where('large_group_id', $largeGroupId)
            ->get()
            ->toArray();
        $projectIds = [];
        foreach ($commissionerProjectVotes as $commissionerProjectVote) {
            $projectIds[] = $commissionerProjectVote->project_id;
        }
        $projectId2PlannedAwardLevel = [];
        $projectPlannedAwardLevels = DB::table('applications')
            ->select('id', 'planned_award_level')
            ->whereIn('id', $projectIds)
            ->get()->toArray();
        foreach ($projectPlannedAwardLevels as $item) {
            $projectId2PlannedAwardLevel[$item->id] = $item->planned_award_level;
        }
        foreach ($commissionerProjectVotes as $i => $commissionerProjectVote) {
            $projectId = $commissionerProjectVote->project_id;
            $project = $projectId2ExpertReviewProject[$projectId];
            $project->planned_award_level = $projectId2PlannedAwardLevel[$projectId];
            $project->vote_result = $commissionerProjectVote->result;
            $project->vote_remark = $commissionerProjectVote->remark;
            $commissionerProjectVotes[$i] = $project;
        }
        usort($commissionerProjectVotes, function ($a, $b) {
            $a = $a->avg_score;
            $b = $b->avg_score;
            return $a < $b ? 1 : ($a == $b ? 0 : -1);
        });
        return [
            'ok' => true,
            'code' => 0,
            'msg' => 'ok',
            'projects' => $commissionerProjectVotes
        ];
    }

    /**
     * 获取指定大组的网评项目分数段表，仅当所有项目已完成专家打分时，才统计分数段
     * @param $largeGroupId
     * @return array[object] 若有项目未完成专家打分，则返回空数组。元素模式：
     *   - avg_score: float, 项目平均分
     *   - segment_projects: int, 本分数段项目数
     *   - accum_projects: int, 累计项目数
     *   - accum_percent: float, 累计项目数占总项目数段百分比，[0, 100]
     * @throws \Exception
     */
    public function getProjectScoreSegmentsAtExpertReview($largeGroupId)
    {
        if (!$this->workflowSvc->hasPhaseBegun(WorkflowService::PHASE_EXPERT_REVIEW, $largeGroupId)) {
            return [];
        }
        if (Project::where('large_group_id', $largeGroupId)->where('status_id', Project::STATUS_EXPERT_REVIEW_TO_SCORE)->count() > 0) {
            return [];
        }
        $statusIds = $this->workflowSvc->getProjectStatusesSincePhase(WorkflowService::PHASE_EXPERT_REVIEW);
        foreach ($statusIds as &$statusId) {
            $statusId = "'$statusId'";
        }
        unset($statusId);
        $statusIdsString = '(' . implode(',', $statusIds) . ')';
        $rows = DB::select("select avg_score, count(*) as `count` from applications where large_group_id = ? and status_id in $statusIdsString group by avg_score order by avg_score desc", [$largeGroupId]);
        $projectScoreSegments = [];
        $accumProjects = 0;
        foreach ($rows as $row) {
            $accumProjects += (int)$row->count;
            $projectScoreSegments[] = (object)[
                'avg_score' => (float)$row->avg_score,
                'segment_projects' => (int)$row->count,
                'accum_projects' => $accumProjects,
                'accum_percent' => 0,
            ];
        }
        foreach ($projectScoreSegments as $projectScoreSegment) {
            $projectScoreSegment->accum_percent = round($projectScoreSegment->accum_projects / $accumProjects * 100, 2);
        }
        return $projectScoreSegments;
    }

    /**
     * 获取指定大组，用于设置授奖等级的分数段表
     * @param $largeGroupId
     * @return array[object] 元素模式：
     *   - avg_score: float, 项目平均分
     *   - segment_projects: int, 本分数段项目数
     *   - accum_projects: int, 累计项目数
     *   - accum_percent: float, 累计项目数占总项目数段百分比，[0, 100]
     * @throws \Exception
     */
    public function getProjectScoreSegmentsForPlanAward($largeGroupId)
    {
        if (!$this->workflowSvc->hasPhaseBegun(WorkflowService::PHASE_PLAN_AWARD, $largeGroupId)) {
            return [];
        }
        $statusIds = $this->workflowSvc->getProjectStatusesSincePhase(WorkflowService::PHASE_PLAN_AWARD);
        foreach ($statusIds as &$statusId) {
            $statusId = "'$statusId'";
        }
        unset($statusId);
        $statusIdsString = '(' . implode(',', $statusIds) . ')';
        $rows = DB::select("select avg_score, count(*) as `count` from applications where large_group_id = ? and status_id in $statusIdsString group by avg_score order by avg_score desc", [$largeGroupId]);
        $projectScoreSegments = [];
        $accumProjects = 0;
        foreach ($rows as $row) {
            $accumProjects += (int)$row->count;
            $projectScoreSegments[] = (object)[
                'avg_score' => (float)$row->avg_score,
                'segment_projects' => (int)$row->count,
                'accum_projects' => $accumProjects,
                'accum_percent' => 0,
            ];
        }
        foreach ($projectScoreSegments as $projectScoreSegment) {
            $projectScoreSegment->accum_percent = round($projectScoreSegment->accum_projects / $accumProjects * 100, 2);
        }
        return $projectScoreSegments;
    }

    /**
     * @param array $data 模式:
     * - commissioner_id: int|string
     * - project_id: int|string
     * - result: string enum, '同意'|'不同意'，必选
     * - remark: string，若 result 为 '不同意', 则必选，否则可选。投票的理由
     * @return array
     * @throws \Exception
     */
    public function committeeVote(array $data)
    {
        $projectId = $data['project_id'];
        $largeGroupId = Project::where('id', $projectId)->value('large_group_id');
        $workflowSvc = new WorkflowService();
        if ($workflowSvc->notAtPhases([WorkflowService::PHASE_VOTE], $largeGroupId)) {
            return [
                'ok' => false,
                'code' => 3,
                'msg' => '现在不是投票阶段，不能执行该操作'
            ];
        }
        $statusId = Project::where('id', $projectId)->value('status_id');
        if ($statusId !== Project::STATUS_VOTE_TO_VOTE) {
            return [
                'ok' => false,
                'code' => 3,
                'msg' => "项目当前状态不是 " . Project::STATUS_VOTE_TO_VOTE . " ，不能执行该操作"
            ];
        }
        $result = $data['result'];
        if (empty($result)) {
            return [
                'ok' => false,
                'code' => 4,
                'msg' => "必选选择 " . Consts::VOTE_RESULT_AGREE . " 或 " . Consts::VOTE_RESULT_DISAGREE,
            ];
        }
        $remark = isset($data['remark']) ? trim($data['remark']) : '';
        if ($result === Consts::VOTE_RESULT_DISAGREE && strlen($remark) === 0) {
            return [
                'ok' => false,
                'code' => 4,
                'msg' => "必选填写不同意的理由",
            ];
        }

        $inTransaction = false;
        try {
            DB::beginTransaction();
            $inTransaction = true;
            DB::table('commissioner_project_votes')
                ->where('project_id', $projectId)
                ->where('commissioner_id', $data['commissioner_id'])
                ->update([
                    'result' => $result,
                    'remark' => $remark,
                ]);
            $remainingVoteCount = DB::table('commissioner_project_votes')
                ->where('project_id', $projectId)
                ->where('result', Consts::VOTE_RESULT_UNVOTED)
                ->count();
            if ($remainingVoteCount == 0) {
                Project::where('id', $projectId)
                    ->update(['status_id' => Project::STATUS_VOTE_TO_SUBMIT_VOTE_RESULT]);
            }
            DB::commit();
            return [
                'ok' => true,
                'code' => 0,
                'msg' => 'ok',
            ];
        } catch (\Exception $e) {
            Log::error('', ['exception' => $e]);
            if ($inTransaction) {
                try {
                    DB::rollBack();
                } catch (\Exception $e2) {
                    Log::error('', ['exception' => $e]);
                }
            }
            return [
                'ok' => false,
                'code' => 2,
                'msg' => '数据库错误',
            ];
        }
    }

    public function voteBatchAgree($data)
    {
        $projectIds = $data['project_ids'];
        $largeGroupId = Project::where('id', $projectIds[0])->value('large_group_id');
        $workflowSvc = new WorkflowService();
        if ($workflowSvc->notAtPhases([WorkflowService::PHASE_VOTE], $largeGroupId)) {
            return [
                'ok' => false,
                'code' => 3,
                'msg' => '现在不是投票阶段，不能执行该操作'
            ];
        }
        $statusIncorrectProjectIds = Project::whereIn('id', $projectIds)
            ->where('status_id', '!=', Project::STATUS_VOTE_TO_VOTE)
            ->pluck('id')->toArray();
        if (!empty($statusIncorrectProjectIds)) {
            return [
                'ok' => false,
                'code' => 3,
                'msg' => "下列项目当前状态不是 " . Project::STATUS_VOTE_TO_VOTE . " ，不能执行该操作：" . implode(',', $statusIncorrectProjectIds),
            ];
        }
        $inTransaction = false;
        try {
            DB::beginTransaction();
            $inTransaction = true;
            $commissionerId = $data['commissioner_id'];
            DB::table('commissioner_project_votes')
                ->whereIn('project_id', $projectIds)
                ->where('commissioner_id', $commissionerId)
                ->update([
                    'result' => Consts::VOTE_RESULT_AGREE,
                    'remark' => '',
                ]);
            $projectIdsString = '(' . implode(',', $projectIds) . ')';
            DB::update("update applications set status_id = ? where id in $projectIdsString and id not in "
                . "(SELECT DISTINCT project_id FROM commissioner_project_votes WHERE project_id in $projectIdsString AND result = ?)",
                [Project::STATUS_VOTE_TO_SUBMIT_VOTE_RESULT, Consts::VOTE_RESULT_UNVOTED]);
            DB::commit();
            return [
                'ok' => true,
                'code' => 0,
                'msg' => 'ok',
            ];
        } catch (\Exception $e) {
            Log::error('', ['exception' => $e]);
            if ($inTransaction) {
                try {
                    DB::rollBack();
                } catch (\Exception $e2) {
                    Log::error('', ['exception' => $e]);
                }
            }
            return [
                'ok' => false,
                'code' => 2,
                'msg' => '数据库错误',
            ];
        }
    }

    /**
     * @param $largeGroupId
     * @return array[object] 元素按 avg_score 降序排序。元素模式：
     *  - id: int, 项目id
     *  - project_name: string
     *  - applicant: string, 申报者名称
     *  - avg_score: string,
     *  - planned_award_level: string, 拟授奖等级
     *  - voted_award_level: string, 拟授奖等级
     *  - agree_count: int, 同意票数
     *  - disagree_count: int, 不同意票数
     *  - remaining_count: int, 待投票数
     */
    public function getProjectVoteStats($largeGroupId)
    {
        $voteStats = DB::select('select project_id, result, count(*) as `count` from commissioner_project_votes where large_group_id = ? group by project_id, result', [$largeGroupId]);
        $projectIdSet = [];
        $projectId2Stats = [];
        foreach ($voteStats as $item) {
            $projectId = $item->project_id;
            $projectIdSet[$projectId] = null;
            if (!isset($projectId2Stats[$projectId])) {
                $projectId2Stats[$projectId] = [
                    Consts::VOTE_RESULT_AGREE => 0,
                    Consts::VOTE_RESULT_DISAGREE => 0,
                    Consts::VOTE_RESULT_UNVOTED => 0,
                ];
            }
            $projectId2Stats[$projectId][$item->result] = $item->count;
        }

        $projects = [];
        if (!empty($projectIdSet)) {
            $projectIds = array_keys($projectIdSet);
            $projects = DB::table('applications as a')
                ->whereIn('a.id', $projectIds)
                ->leftJoin('users as b', 'a.user_id', '=', 'b.id')
                ->select('a.id', 'a.project_name', 'b.name as applicant', 'a.avg_score', 'planned_award_level', 'voted_award_level')
                ->orderBy('ranking')
                ->get()->toArray();
        }
        foreach ($projects as $project) {
            $stats = $projectId2Stats[$project->id];
            $project->agree_count = $stats[Consts::VOTE_RESULT_AGREE];
            $project->disagree_count = $stats[Consts::VOTE_RESULT_DISAGREE];
            $project->remaining_count = $stats[Consts::VOTE_RESULT_UNVOTED];
        }
        usort($projects, function ($a, $b) {
            $a = (float)$a->avg_score;
            $b = (float)$b->avg_score;
            return $a < $b ? 1 : ($a == $b ? 0 : -1);
        });
        return $projects;
    }

    public function getAwardedProjects($largeGroupId)
    {
        $projects = DB::table('applications as a')
            ->where('large_group_id', $largeGroupId)
            ->where('status_id', Project::STATUS_AWARD_AWARD)
            ->leftJoin('users as b', 'a.user_id', '=', 'b.id')
            ->select('a.id', 'a.project_name', 'a.avg_score', 'a.ranking_percent', 'final_award_level',
                'b.name as applicant_name', 'b.tel as applicant_tel', 'b.work_org_name as applicant_work_org_name')
            ->get()->toArray();

        $awardType = Consts::LARGE_GROUP_ID_TO_AWARD_TYPE[$largeGroupId];
        $awards = Consts::AWARD_TYPE_TO_AWARDS[$awardType];
        usort($projects, function ($a, $b) use ($awards) {
            $awardIndex1 = array_search($a->final_award_level, $awards);
            $awardIndex2 = array_search($b->final_award_level, $awards);
            $order = $awardIndex1 < $awardIndex2 ? -1 : ($awardIndex1 == $awardIndex2 ? 0 : 1);
            if ($order !== 0) {
                return $order;
            }
            $c = (float)$a->avg_score;
            $d = (float)$b->avg_score;
            return $c < $d ? 1 : ($c == $d ? 0 : -1);
        });
        $award2count = [];
        foreach ($projects as $project) {
            $award = $project->final_award_level;
            if (!isset($award2count[$award])) {
                $award2count[$award] = 0;
            }
            $award2count[$award]++;
        }
        $perAward = [];
        foreach ($award2count as $award => $count) {
            $perAward[] = (object)['award' => $award, 'count' => $count];
        }
        $stats = (object)[
            'total_count' => count($projects),
            'per_award' => $perAward,
        ];
        return [
            'ok' => true,
            'code' => 0,
            'msg' => 'ok',
            'stats' => $stats,
            'projects' => $projects,
        ];
    }

}
